package com.star.wellconnect.sqlite;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {
	public static final int EMPTY = 0;
	public static final int FALSE = 1;
	public static final int TRUE = 2;
	
	// Database Version
	private static final int DATABASE_VERSION = 1;
	//Database Name
	private static final String DATABASE_NAME = "WellConnectDB";
	
	//Patient Table
	private static final String patientTable = "Patients";
	private static final String patientID = "Username";
	private static final String patientPassword = "Password";
	private static final String patientPoints = "Points";
	private static final String patientName = "Patient_Name";
	private static final String patientEmail = "Email";
	private static final String patientPhone = "Phone_Number";
	private static final String patientImage = "Image";
	
	//CareGiver Table
	private static final String cgTable = "CareGivers";
	private static final String cgID = "UserName";
	private static final String cgPassword = "Password";
	private static final String cgName = "Name";
	private static final String cgEmail = "Email";
	private static final String cgPhone = "Phone_Number";
	private static final String cgImage = "Image";

	
	//Appointments Table
	private static final String apptTable = "Appointments";
	private static final String apptID = "Appointments_ID";
	private static final String apptName = "Appointment_Name";
	private static final String apptDoctor = "Doctor_Name";
	private static final String apptSpecial = "Doctor_Specialization";
	private static final String apptDate = "Date";
	private static final String apptTime = "Time";
	private static final String apptAddress = "Address";
	private static final String apptPhone = "Phone Number";
	private static final String apptNotes = "Notes";
	
	private static final String dt = "DT"; // save when data was entered into database
	
	//Moods Table
	private static final String moodTable = "Moods";
	private static final String moodID = "Mood_ID";
	private static final String moodDate = "Date-Time";
	
	// add all moods
	//
	//
	
	//Medication Table
	private static final String medTable = "Medications";
	private static final String medID = "Med_ID";
	private static final String medName = "MedName";
	private static final String medNickname = "MedNickname";
	private static final String medDosage = "Dosage";
	private static final String medRefill = "Refill_Days";
	
	//Strategies
	private static final String stratTable = "Strategies";
	private static final String stratID = "Strategy_ID";
	private static final String stratName = "Strategy_Name";
	private static final String stratPoints = "Points_Added";
	
	//Rewards
	private static final String rewardTable = "Rewards";
	private static final String rewardID = "Reward_ID";
	private static final String rewardName = "Reward_Name";
	private static final String rewardPoints = "Points_Required";
	
//Many-to-Many relation Tables
	
	//Patient-CareGiver
	private static final String pcgTable = "Patient_CareGiver";
	private static final String pcgPatient = "Patient_ID";
	private static final String pcgCareGiver = "CareGiver_ID";
	
	//Patient-Med
	private static final String pmTable = "Patient_Meds";
	private static final String pmPatient = "Patient_ID";
	private static final String pmMed = "Med_ID";
	
	
	public DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		//SQL statement to create Patient table
		
		db.execSQL("CREATE TABLE IF NOT EXISTS " + patientTable + " (" //create patient table
		        + patientID + " VARCHAR(255) PRIMARY KEY, " + patientPassword+ " VARCHAR(12) NOT NULL, "
		        + patientPoints + " VARCHAR(255) NOT NULL, "
		        + patientName + " VARCHAR(255) NOT NULL, " + patientEmail + " VARCHAR(255) NOT NULL, " 
		        + patientPhone + " VARCHAR(255) NOT NULL, " + patientImage + " BLOB);");
		
		db.execSQL("CREATE TABLE IF NOT EXISTS " + cgTable + " (" //create caregiver table
		        + cgID + " VARCHAR(255) PRIMARY KEY, " + cgPassword+ " VARCHAR(12) NOT NULL, "
		        + cgName + " VARCHAR(255) NOT NULL, " + cgEmail + " VARCHAR(255) NOT NULL, " 
		        + cgPhone + " VARCHAR(255) NOT NULL, " + cgImage + " BLOB);");
		
		db.execSQL("CREATE TABLE IF NOT EXISTS " + apptTable + " (" //create appts table
	        	+ apptID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + patientID + " VARCHAR, " 
				+ apptName + " VARCHAR, " + apptDoctor + " VARCHAR, " 
        		+ apptDate + " VARCHAR, " + apptTime + " VARCHAR, " 
				+ apptAddress+ " VARCHAR(255), " + apptNotes + " TEXT, "
        		+dt+ " DATETIME default current_timestamp, " +
        		"FOREIGN KEY ("+patientID+") REFERENCES "+ patientTable +"("+patientID+"));");
		
		// need to create moods table!!!!!!
		// need to add all the moods
		db.execSQL("CREATE TABLE IF NOT EXISTS " + moodTable + " (" //create mood table
	        	+ moodID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + patientID + " VARCHAR, " 
				+ dt + " DATETIME default current_timestamp, "
				//need to add moods: happy, sad, stress, etc....
        		+ "FOREIGN KEY ("+patientID+") REFERENCES "+ patientTable +"("+patientID+"));");
		
		db.execSQL("CREATE TABLE IF NOT EXISTS " + medTable + " (" //create med table
	        	+ medID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + medName + " VARCHAR, " 
				+ medNickname + " VARCHAR, " + medDosage + " VARCHAR, " 
				+ dt + " DATETIME default current_timestamp, "
				+ medRefill + " VARCHAR);");
		
		db.execSQL("CREATE TABLE IF NOT EXISTS " + stratTable + " (" //create strategy table
	        	+ stratID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + patientID + " VARCHAR, " 
				+ stratName + " VARCHAR, " + stratPoints + " VARCHAR, " 
				+ dt + " DATETIME default current_timestamp, "
        		+ "FOREIGN KEY ("+patientID+") REFERENCES "+ patientTable +"("+patientID+"));");
		
		db.execSQL("CREATE TABLE IF NOT EXISTS " + rewardTable + " (" //create reward table
	        	+ rewardID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + patientID + " VARCHAR, " 
				+ rewardName + " VARCHAR, " + rewardPoints + " VARCHAR, " 
				+ dt + " DATETIME default current_timestamp, "
        		+ "FOREIGN KEY ("+patientID+") REFERENCES "+ patientTable +"("+patientID+"));");

		db.execSQL("CREATE TABLE IF NOT EXISTS " + pcgTable + " (" //create patient-caregiver table
	        	+ pcgPatient + " VARCHAR, " + pcgCareGiver + " VARCHAR, " 
        		+ "FOREIGN KEY ("+pcgPatient+") REFERENCES "+ patientTable +"("+patientID+"));"
				+ "FOREIGN KEY ("+pcgCareGiver+") REFERENCES "+ cgTable +"("+cgID+"));");
		
		db.execSQL("CREATE TABLE IF NOT EXISTS " + pmTable + " (" //create patient-medication table
	        	+ pmPatient + " VARCHAR, " + pmMed + " VARCHAR, " 
        		+ "FOREIGN KEY ("+pmPatient+") REFERENCES "+ patientTable +"("+patientID+"));"
				+ "FOREIGN KEY ("+pmMed+") REFERENCES "+ medTable +"("+medID+"));");

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + patientTable);
		db.execSQL("DROP TABLE IF EXISTS " + cgTable);
		db.execSQL("DROP TABLE IF EXISTS " + apptTable);
		db.execSQL("DROP TABLE IF EXISTS " + moodTable);
		db.execSQL("DROP TABLE IF EXISTS " + medTable);
		db.execSQL("DROP TABLE IF EXISTS " + stratTable);
		db.execSQL("DROP TABLE IF EXISTS " + rewardTable);
		db.execSQL("DROP TABLE IF EXISTS " + pcgTable);
		db.execSQL("DROP TABLE IF EXISTS " + pmTable);
		
		onCreate(db);
	}
	
	/// ALL QUERY FOR ALL THE TABLE \\\
	/// PATIENT \\\
	public long addPatient(Patient patient, String password){
		SQLiteDatabase db = this.getWritableDatabase();
		long retVal = -1;
		try{
			ContentValues values = new ContentValues();
			values.put(patientID, patient.username);
			values.put(patientPassword, password);
			values.put(patientName, patient.name);
			values.put(patientPoints, patient.points);
			values.put(patientEmail, patient.email);
			values.put(patientPhone, patient.phone);
			values.put(patientImage, patient.image);
			
			retVal = db.insert(patientTable, null, values);
			return retVal;
		}catch(Exception e) {
			return retVal;
		}finally{
			db.close();
		}
	}
	
	public Patient getPatient(String username){
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(patientTable, new String[]{
				patientID, patientPoints, patientName,
				patientEmail, patientPhone, patientImage
		},  patientID + "=?", new String[] { username}, null, null, null, null);
		
		try{
			if(cursor.moveToFirst()){
				Patient patient = new Patient(cursor.getString(0), cursor.getString(1),
						cursor.getString(2), cursor.getString(3), cursor.getString(4),
						cursor.getBlob(5));
				return patient;
			}else{
				return null;
			}
		}finally{
			if(cursor != null && !cursor.isClosed())
				cursor.close();
		}
	}
	
	public String getPatientPassword(String username){
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(patientTable, new String[]{
				patientPassword
		},  patientID + "=?", new String[] { username}, null, null, null, null);
		try{
			if(cursor.moveToFirst()){
				return cursor.getString(0);
			}else{
				return null;
			}
		}finally{
			if(cursor != null && !cursor.isClosed())
				cursor.close();
		}
	}
	
	public List<Patient> getAllPatients(){
		List<Patient> patientList = new ArrayList<Patient>();
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery("SELECT * FROM " + patientTable, null);
		
		try{
			if(cursor.moveToFirst()){
				do {
					Patient patient = new Patient(cursor.getString(0), cursor.getString(2), 
							cursor.getString(3), cursor.getString(4),
							cursor.getString(5), cursor.getBlob(6));
					patientList.add(patient);
				} while(cursor.moveToNext());
			}
			
			return patientList;
		}finally{
			if(cursor != null && !cursor.isClosed())
				cursor.close();
		}
	}
	
	public int getPatientsCount(){
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery("SELECT  * FROM " + patientTable, null);
		try{
			return cursor.getCount();
		}finally{
			if(cursor != null && !cursor.isClosed())
				cursor.close();
		}
	}
	
	public int updatePatient(Patient patient){
		SQLiteDatabase db = this.getWritableDatabase();
		int retVal = -1;
		try{
			ContentValues values = new ContentValues();
			values.put(patientName, patient.name);
			values.put(patientPoints, patient.points);
			values.put(patientEmail, patient.email);
			values.put(patientPhone, patient.phone);
			values.put(patientImage, patient.image);
			
			retVal = db.update(patientTable, values, patientID + " =?",
					new String[]{patient.username});
			return retVal;
		}catch(Exception e){
			return retVal;
		}finally{
			db.close();
		}
	}
	
	public int updatePatientPassword(String username, String password){
		SQLiteDatabase db = this.getWritableDatabase();
		int retVal = -1;
		try{
			ContentValues values = new ContentValues();
			values.put(patientPassword, password);
			
			retVal = db.update(patientTable, values, patientID + " =?",
							new String[]{username});
			return retVal;
		}catch(Exception e){
			return retVal;
		}finally{
			db.close();
		}
	}
	
	public void deletePatient(Patient patient){
		SQLiteDatabase db = this.getWritableDatabase();
		try{
			db.delete(patientTable, patientID + " =?",
					new String[]{patient.username});
		}finally{
			db.close();
		}
	}
	
	public int isValidUser(String username, String password){
		SQLiteDatabase db  = this.getReadableDatabase();
		Cursor cursor = db.query(patientTable, new String[]{
				patientID, patientPassword
		},  patientID + "=?", new String[] {username}, null, null, null, null);
		
		try{
			if(cursor.moveToFirst()){
				return (cursor.getString(0).equals(username)
						&& cursor.getString(1).equals(password)? TRUE: FALSE);
			}else{
				return EMPTY;
			}
		}finally{
			if(cursor != null && !cursor.isClosed())
				cursor.close();
		}
	}
	
	public boolean usernameTaken(String username){
		SQLiteDatabase db  = this.getReadableDatabase();
		Cursor cursor = db.query(patientTable, new String[]{
				patientID, patientPassword
		},  patientID + "=?", new String[] {username}, null, null, null, null);

		try{
			return cursor.moveToFirst();
		}finally{
			if(cursor != null && !cursor.isClosed())
				cursor.close();
		}
	}
	
	/***
	 * Add an appointment to the database
	 * @param appointment
	 * @param username
	 * @return -1 for failure, otherwise, success
	 */
	public long addAppointment(Appointment appt, String username){
		SQLiteDatabase db = this.getWritableDatabase();
		long retVal = -1;
		try{
			ContentValues values = new ContentValues();
			values.put(apptName, appt.name);
			values.put(apptDoctor, appt.doctor);
			values.put(apptDate, appt.date);
			values.put(apptTime, appt.time);
			values.put(apptAddress, appt.address);
			values.put(apptNotes, appt.notes);
			values.put(patientID, username);
			
			retVal = db.insert(apptTable, null, values);
			return retVal;
		}catch(Exception e) {
			return retVal;
		}finally{
			db.close();
		}
	}
	
	/***
	 * Retrieve a specific appointment from the database
	 * @param username
	 * @param apptId
	 * @return Appointment
	 */
	public Appointment getAppointment(String username, int apptId){
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(apptTable, new String[]{
				apptID, patientID, apptName, apptDoctor, 
				apptDate, apptTime, apptAddress, apptNotes
		},  patientID + " = '"+username+"' AND " + apptID + " = '"+apptId+"'", 
		null, null, null, null);
		
		try{
			if(cursor.moveToFirst()){
				Appointment appt = new Appointment(cursor.getInt(0), cursor.getString(2),
						cursor.getString(3), cursor.getString(4), cursor.getString(5),
						cursor.getString(6), cursor.getString(7));
				return appt;
			}else{
				return null;
			}
		}finally{
			if(cursor != null && !cursor.isClosed())
				cursor.close();
		}
	}
	
	/***
	 * Return all appointments for this user
	 * @param username
	 * @return List of appointments
	 */
	private List<Appointment> getAllAppointments(String username){
		List<Appointment> apptList = new ArrayList<Appointment>();
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery("SELECT * FROM " + apptTable +
				" WHERE " + patientID + " = '" + username + "' ORDER BY " + 
				apptDate +" ASC, " + apptTime + " ASC", null);
		
		try{
			if(cursor.moveToFirst()){
				do {
					Appointment appt = new Appointment(cursor.getInt(0), cursor.getString(2),
							cursor.getString(3), cursor.getString(4), cursor.getString(5), 
							cursor.getString(6), cursor.getString(7));
					apptList.add(appt);
				} while(cursor.moveToNext());
			}
			
			return apptList;
		}finally{
			if(cursor != null && !cursor.isClosed())
				cursor.close();
		}
	}
	
	/***
	 * Return all appointments for this user on or after a specific date and time
	 * @param username
	 * @param date
	 * @param time
	 * @return List of appointments
	 */
	public List<Appointment> getAllAppointments(String username, Date now){
		List<Appointment> appts = getAllAppointments(username);
		List<Appointment> retAppts = new ArrayList<Appointment>();
		Date dt;
		DateFormat dtf = new SimpleDateFormat(
				AppPreferences.SQL_DATE + " " + AppPreferences.MILITARY_TIME, Locale.US);
		
		for(Appointment appt: appts){
			try{
				dt = dtf.parse(appt.date + " " + appt.time);
				if(now.before(dt))
					retAppts.add(appt);
			}catch(Exception e){
				Log.e("GetAllAppointmentsError", e.toString());
			}
		}
		
		return retAppts;
	}

	/***
	 * Return all appointments for this user on or after a specific date and time
	 * @param username
	 * @param date
	 * @param time
	 * @return List of appointments
	 */
	public List<Appointment> getAllAppointmentsByDay(String username, String date, String time){
		List<Appointment> apptList = new ArrayList<Appointment>();
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery("SELECT * FROM " + apptTable +
				" WHERE " + patientID + " = '" + username + "' AND " +
				"date(" + apptDate +") = date(" + date + ") AND " +
				"time(" + apptTime +") >= time(" + time + ")", null);
		
		try{
			if(cursor.moveToFirst()){
				do {
					Appointment appt = new Appointment(cursor.getInt(0), cursor.getString(2),
							cursor.getString(3), cursor.getString(4), cursor.getString(5),
							cursor.getString(6), cursor.getString(6));
					apptList.add(appt);
				} while(cursor.moveToNext());
			}
			
			return apptList;
		}finally{
			if(cursor != null && !cursor.isClosed())
				cursor.close();
		}
	}
	
	/***
	 * Return the number of appointments for this user
	 * @param username
	 * @return appointment count
	 */
	public int getAppointmentCount(String username){
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery("SELECT * FROM " + apptTable +
				" WHERE " + patientID + " = '" + username + "'", null);
		try{
			return cursor.getCount();
		}finally{
			if(cursor != null && !cursor.isClosed())
				cursor.close();
		}
	}
	
	/***
	 * Return the number of appointments for this user after a specific date and time
	 * @param username
	 * @return appointment count
	 */
	public int getAppointmentCount(String username, String date, String time){
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery("SELECT * FROM " + apptTable +
				" WHERE " + patientID + " = '" + username + "' AND " +
				"date(" + apptDate +") >= date(" + date + ") AND " +
				"time(" + apptTime +") >= time(" + time + ")", null);
		
		try{
			return cursor.getCount();
		}finally{
			if(cursor != null && !cursor.isClosed())
				cursor.close();
		}
	}
	
	/***
	 * Update a particular appointment
	 * @param username
	 * @param appt
	 * @return -1 for failure, otherwise success
	 */
	public int updateAppointment(String username, Appointment appt){
		SQLiteDatabase db = this.getWritableDatabase();
		int retVal = -1;
		try{
			ContentValues values = new ContentValues();
			values.put(apptName, appt.name);
			values.put(apptDoctor, appt.doctor);
			values.put(apptDate, appt.date);
			values.put(apptTime, appt.time);
			values.put(apptAddress, appt.address);
			values.put(apptNotes, appt.notes);
			values.put(patientID, username);
			
			retVal = db.update(apptTable, values, "WHERE " + patientID +
					" = '" + username + "' AND " + apptID +" = '" + appt.getID(), null);
			return retVal;
		}catch(Exception e){
			return retVal;
		}finally{
			db.close();
		}
	}
	
	/***
	 * delete an appointment
	 * @param apptId
	 */
	public void deleteAppointment(int apptId){
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(apptTable, apptID + " =?",
				new String[]{"" + apptId});
		db.close();
	}
}